# load modules and functions 
import os
import numpy as np
import pandas as pd
from pandas import DataFrame
import matplotlib.pyplot as plt
import seaborn as sns
import plotly.express as px
import plotly.graph_objects as go
from IPython.display import Image

from colorama import Fore, Back, Style

import My_Function_050824_M_Filimonov as mvf # loading functions written by me for the Project from a file 

import importlib
importlib.reload(mvf)  # Перезагружаем модуль после изменений

<module 'My_Function_050824_M_Filimonov' from 'C:\\Users\\fmikh\\Documents\\ICH\\FIN_PRJ\\ICH_DataAnalysisProject_Python_PowerBI\\My_Function_050824_M_Filimonov.py'>

# load DataFrames:
#calls = pd.read_pickle("01_calls.pkl")
contacts = pd.read_pickle("02_contacts.pkl")
spend = pd.read_pickle("03_spend.pkl")
deals = pd.read_pickle("04_deals.pkl")

#deals.select_dtypes(include=["number"])

# сравниваем число лидов в Deals и Contacts и берем там где больше
# логика:
# - пользователь не определился, его контакты занесены в Contacts, но сделка не открыта
# - контакты пользователя занесены в Contacts, но менеджер не отреагировал
# - сделка автоматически создалась при регистрации, но по каким-то причинам данные не занесены в Contacts

deal_lids = deals.Contact_Name.nunique()
print(f"Количество лидов в таблице Deals: {deal_lids}")

contacts_lids = contacts.Id.nunique()
print(f"Количество лидов в таблице Contacts: {contacts_lids}")

if deal_lids >= contacts_lids:
    UA_CONST = deal_lids
else:
    UA_CONST = contacts_lids
    
print(Fore.GREEN+f"Решение по количеству лидов: {UA_CONST}"+Style.RESET_ALL)

AC_CONST = spend['Spend'].sum()  
print(Fore.RED+f"Маркетинговый бюджет: {AC_CONST:.2f}€"+Style.RESET_ALL)

LTC_CONST = AC_CONST / UA_CONST
print(Fore.BLUE+f"Стоимость привлечения потенциального клиента: {LTC_CONST:.2f}€"+Style.RESET_ALL)

Количество лидов в таблице Deals: 18089
Количество лидов в таблице Contacts: 18548
Решение по количеству лидов: 18548
Маркетинговый бюджет: 149523.45€
Стоимость привлечения потенциального клиента: 8.06€

######################################### calculate metrics by Product only
product_stats_p = deals[deals.Product != "unknown"].groupby(["Product"], observed=True).agg(
    B=("Stage", lambda x: ((x == "Payment Done") & (deals.loc[x.index, "T"] > 0)).sum()),         # "Stage"= "Payment Done" & T > 0
    T=("T", lambda x: x[deals["Stage"] == "Payment Done"].sum()),                                 # Payments count (Transactions)
    AOV=("AOV", lambda x: x[deals["Stage"] == "Payment Done"].mean() if x[deals["Stage"] == "Payment Done"].sum() > 0 else 0),  
    Revenue=("Paid", lambda x: x[deals["Stage"] == "Payment Done"].sum()) 
).reset_index()

product_stats_p["UA"] = UA_CONST                                            # Scaling unit flow        
product_stats_p["AC"] = AC_CONST                                            # Acquisition Cost
product_stats_p['LTC'] = LTC_CONST                                          # LifeTime Cost

product_stats_p["C1"] = product_stats_p["B"] / product_stats_p["UA"] * 100   # Conversion rate to the first sale
product_stats_p['CLTC'] = product_stats_p['LTC'] / product_stats_p['C1']     # Customer Lifetime cost
product_stats_p['APC'] = product_stats_p['T'] / product_stats_p['B']         # Average Payment Count
product_stats_p['CLTV'] = product_stats_p['AOV'] * product_stats_p['APC']    # Customer LifeTime Value
product_stats_p['LTV'] = product_stats_p['CLTV'] * product_stats_p['C1']      # LifeTime Value
product_stats_p['CM'] = (product_stats_p['CLTV'] - product_stats_p['CLTC']) * product_stats_p['B'] # Contribution margin

# fillna NaN and inf
columns_to_fix = ["AOV","CLTC", "APC", "CLTV", "LTV", "CM"]
product_stats_p[columns_to_fix] = product_stats_p[columns_to_fix].fillna(0).replace([float('inf'), float('-inf')], 0)

# Set float display format
pd.set_option('display.float_format', '{:,.2f}'.format)

# filter produkts with T > 0 (Payment > 0, but Month of Study ==0)) 
product_stats_p = product_stats_p[product_stats_p["T"] > 0]

#display(product_stats_p)

# Add Total row, sum only number columns
total_p = product_stats_p.select_dtypes(include=['number']).sum()

# calculation metrics
total_p["UA"] = UA_CONST 
total_p['AC'] = AC_CONST
total_p['LTC'] = LTC_CONST

total_p['AOV'] = total_p['Revenue'] / total_p['T'] if total_p['T'] != 0 else 0
total_p['APC'] = total_p['T'] / total_p['B'] if total_p['B'] != 0 else 0
total_p['C1']  = total_p['B'] / total_p['UA'] if total_p['UA'] != 0 else 0
total_p['CLTC'] = total_p['LTC'] / total_p['C1'] 
total_p['CLTV'] = total_p["AOV"] * total_p['APC']
total_p['LTV'] = total_p['CLTV'] * total_p['C1']
total_p['CM'] = (total_p['CLTV'] - total_p['CLTC']) * total_p['B']

total_p["Product"] = "TOTAL:"

# concat total_p row
print(Back.YELLOW + "Unit Economics Summary Table (Product)" + Style.RESET_ALL)
product_stats2 = pd.concat([product_stats_p, total_p.to_frame().T], ignore_index=True)

display(product_stats2)

######################################### visualisation metrics by Product only
# Create a bar chart with two indicators: "B" and "UA"
fig = px.bar(
    product_stats_p, 
    x="Product", 
    y=["UA","B" ],
    title="Units Acquisition (UA), Buyers (B), Conversion Rate (C1) per Product (log scale)",
    text_auto=True,
    labels={"B": "Buyers", "UA": "Units Acquisition"},
    color_discrete_map={"B": "skyblue", "UA": "lightgrey"},
    barmode="group",  # Placement of bars nearby
    log_y=True  # Logarithmic scale for Y
)

# Add a conversion rate line
fig.add_trace(
    go.Scatter(
        x=product_stats_p["Product"],
        y=product_stats_p["C1"],
        mode="lines+markers+text",
        text=[f"{x:.2f}%" for x in product_stats_p["C1"]],
        textposition="top center",
        name="Conversion Rate (%)",
        line=dict(color="red", width=2),
        marker=dict(size=10),
        textfont=dict(color="red", size=14)        
    )
)
fig.update_layout(
    yaxis=dict(title="UA, B number", showgrid=False),
    xaxis=dict(title="Product", tickangle=0)  
)

fig.show()

Unit Economics Summary Table (Product)

############# calculate metrics by Product and Education Type
product_stats = deals[(deals.Product !="unknown")&(deals.Education_Type !="unknown")].groupby(["Product","Education_Type"], observed=True).agg(    
    B=("Stage", lambda x: ((x == "Payment Done") & (deals.loc[x.index, "T"] > 0)).sum()),         # "Stage"= "Payment Done" & T > 0
    T=("T", lambda x: x[deals["Stage"] == "Payment Done"].sum()),                                 # Payments count (Transactions)
    AOV=("AOV", lambda x: x[deals["Stage"] == "Payment Done"].mean() if x[deals["Stage"] == "Payment Done"].sum() > 0 else 0),  
    Revenue=("Paid", lambda x: x[deals["Stage"] == "Payment Done"].sum()) 
).reset_index()

product_stats["UA"] = UA_CONST                                            # Scaling unit flow        
product_stats["AC"] = AC_CONST                                            # Acquisition Cost
product_stats['LTC'] = LTC_CONST                                          # LifeTime Cost

product_stats["AC"] = spend['Spend'].sum()                             # Acquisition Cost
product_stats["C1"] = product_stats["B"] / product_stats["UA"] * 100   # Conversion rate to the first sale
product_stats['LTC'] = product_stats['AC'] / product_stats['UA']       # LifeTime Cost
product_stats['CLTC'] = product_stats['LTC'] / product_stats['C1']       # Customer Lifetime cost
product_stats['APC'] = product_stats['T'] / product_stats['B']         # Average Payment Count
product_stats['CLTV'] = product_stats['AOV'] * product_stats['APC']    # Customer LifeTime Value
product_stats['LTV'] = product_stats['CLTV'] * product_stats['C1']  # LifeTime Value
product_stats['CM'] = (product_stats['CLTV'] - product_stats['CLTC']) * product_stats['B'] # Contribution margin

# fillna NaN and inf
columns_to_fix = ["AOV","CLTC", "APC", "CLTV","LTV", "CM"]
product_stats[columns_to_fix] = product_stats[columns_to_fix].fillna(0).replace([float('inf'), float('-inf')], 0)

# filter produkts with T > 0 (Payment > 0, but Month of Study ==0)) 
product_stats = product_stats[product_stats["T"] > 0]

# Add total_pe row, sum only number columns
total_pe = product_stats.select_dtypes(include=['number']).sum()

# calculation metrics
total_pe["UA"] = UA_CONST 
total_pe['AC'] = AC_CONST
total_pe['LTC'] = LTC_CONST

total_pe['AOV'] = total_pe['Revenue'] / total_pe['T'] if total_pe['T'] != 0 else 0
total_pe['APC'] = total_pe['T'] / total_pe['B'] if total_pe['B'] != 0 else 0
total_pe['C1']  = total_pe['B'] / total_pe['UA'] if total_pe['UA'] != 0 else 0
total_pe['CLTC'] = total_pe['LTC'] / total_pe['C1'] 
total_pe['CLTV'] = total_pe["AOV"] * total_pe['APC']
total_pe['LTV'] = total_pe['CLTV'] * total_pe['C1']
total_pe['CM'] = (total_pe['CLTV'] - total_pe['CLTC']) * total_pe['B']

total_pe["Product"] = "TOTAL:"
total_pe["Education_Type"] = ""

# concat df and total_pe row
product_stats_t = pd.concat([product_stats, total_pe.to_frame().T], ignore_index=True)
print(Back.YELLOW + "Unit Economics Summary Table (Product & Education Type)" + Style.RESET_ALL)
pd.set_option('display.float_format', '{:,.2f}'.format)
display(product_stats_t)

Unit Economics Summary Table (Product & Education Type)

########## Visualization by products, number of leads, customers and conversion rate
product_stats = product_stats.sort_values(by="Product")

# Create a stacked bar chart for Buyers (B) per Product, split by Morning/Evening
fig = px.bar(
    product_stats, 
    x="Product", 
    y="B",     
    color="Education_Type", 
    log_y=True,
    barmode="stack",  
    text_auto=True,  
    labels={"B": ""},
    title="Buyers (B), Conversion Rate (C1) per Product and Education Type (log scale)",
    color_discrete_map={"Evening": "#FFDDC1","Morning": "lightblue"},  # #FFDDC1, lightblue   
    category_orders={"Education_Type": ["Evening", "Morning"],  # Обратный порядок для стэка
                     "Product": ["Digital Marketing","UX/UI Design","Web Developer","Data Analytics"]}
)


# Apply log scale correction
#fig.update_layout(yaxis_type="log")  

# Filter data for Morning and Evening
morning_stats = product_stats[product_stats["Education_Type"] == "Morning"]
evening_stats = product_stats[product_stats["Education_Type"] == "Evening"]


# Filter Evening data, removing zero values
morning_stats_filtered = morning_stats[morning_stats["C1"] > 0]
evening_stats_filtered = evening_stats[evening_stats["C1"] > 0]


morning_stats_filtered = morning_stats_filtered.sort_values(by="B",ascending=False)
evening_stats_filtered = evening_stats_filtered.sort_values(by="B",ascending=False)


# Add a conversion rate line for Morning
fig.add_trace(
    go.Scatter(
        x=morning_stats_filtered["Product"],
        y=morning_stats_filtered["C1"],
        mode="lines+markers+text",  
        text=[f"{x:.2f}%" for x in morning_stats_filtered["C1"]],  
        textposition="bottom center",  
        name="Conversion Rate - Morning",
        line=dict(color="blue", width=2),
        marker=dict(size=6, color="blue"),
        textfont=dict(color="blue", size=12),
        hovertemplate="Product: %{x}<br>Education Type: Morning<br>Conversion Rate: %{y}%<extra></extra>"
        
    )
)

#   Add a conversion rate line for Evening (only non-zero values)
fig.add_trace(
    go.Scatter(
        x=evening_stats_filtered["Product"],
        y=evening_stats_filtered["C1"],
        mode="lines+markers+text",  
        text=[f"{x:.2f}%" for x in evening_stats_filtered["C1"]],  
        textposition="bottom center",  
        name="Conversion Rate - Evening",
        line=dict(color="orange", width=2),
        marker=dict(size=6, color="orange"),
        textfont=dict(color="orange", size=12),
        hovertemplate="Product: %{x}<br>Education Type: Evening<br>Conversion Rate: %{y}%<extra></extra>"  
    )
)

fig.update_layout(
    legend=dict(x=1.001, y=1, traceorder="normal"),  # reversed
    yaxis=dict(title="Bayers number", showgrid=False),
    xaxis=dict(title="Product", tickangle=0)      
)
fig.show()

product_stats = product_stats.sort_values(by="Product")

# Create a stacked bar chart for Buyers (B) per Product, split by Morning/Evening
fig = px.bar(
    product_stats, 
    x="Product", 
    y="CM",     
    color="Education_Type",  
    barmode="stack",  
    text_auto=True,  
    labels={"CМ": "Contribution Margin, €"},
    title="Contribution Margin per Product and Education Type (log scale)",
    color_discrete_map={"Morning": "lightblue", "Evening": "#FFDDC1"},  # #FFDDC1, lightblue
    category_orders={"Education_Type": ["Evening", "Morning"],  # Обратный порядок для стэка
                     "Product": ["Digital Marketing","UX/UI Design","Web Developer","Data Analytics"]}
)
fig.update_layout(
    legend=dict(x=1.001, y=1, traceorder="normal"),  # reversed
    yaxis=dict(title="Contribution Margin, €", showgrid=False),
    xaxis=dict(title="Product", tickangle=0)      
)
fig.show()

def simulate_metric_changes(df, metrics_to_test, change_percent=10):
    """
    Modifies each metric separately and analyzes its impact on CM.
    
    :param df: DataFrame with economic indicators.
    :param metrics_to_test: Dictionary of metrics {metric name: increase (True) or decrease (False)}.
    :param change_percent: Percentage change for each metric (default is 10%).
    :return: DataFrame with CM changes, ranked by decreasing CM Change (%).
    """
    results = []

    for metric, increase in metrics_to_test.items():
        modified_df = df.copy()
        
        #  Modify ONLY the current metric
        if metric in modified_df.columns:
            factor = 1 + (change_percent / 100) if increase else 1 - (change_percent / 100)
            modified_df[metric] *= factor

        #  Recalculate dependent metrics
        modified_df['CLTC'] = modified_df['LTC'] / modified_df['C1']   # Customer Lifetime Cost
        modified_df['APC'] = modified_df['T'] / modified_df['B']       # Average Payment Count
        modified_df['CLTV'] = modified_df['AOV'] * modified_df['APC']  # Customer Lifetime Value
        modified_df['LTV'] = modified_df['CLTV'] * modified_df['C1']   # Lifetime Value
        modified_df['CM'] = (modified_df['CLTV'] - modified_df['CLTC']) * modified_df['B'] # Contribution Margin

        #  Calculate CM change
        modified_df["CM_Change"] = (modified_df["CM"] - df["CM"]) / df["CM"] * 100  # % CM change
        
        #  Store results for the current metric
        modified_df["Metric Tested"] = metric
        
        results.append(modified_df[["Product", "Education_Type", "Metric Tested", "CM", "CM_Change"]])

    #  Combine all results and rank by decreasing CM Change (%)
    final_df = pd.concat(results).sort_values(by="CM_Change", ascending=False)
    
    #  Select only numerical columns
    numeric_cols = final_df.select_dtypes(include=["number"]).columns

    #  Replace `NaN` and `inf` only in numerical columns
    final_df[numeric_cols] = final_df[numeric_cols].fillna(0).replace([np.inf, -np.inf], 0)

    return final_df

##########################  Test data
metrics_to_test = {"UA":True,"LTC":False,"AOV": True, "C1": True, "CLTC": False}  # True - increase, False - decrease metric value
change_percent = 10  # 10% change

##########################  Run function
modified_product_stats = simulate_metric_changes(product_stats, metrics_to_test, change_percent)

##########################  Display all results
modified_product_stats = modified_product_stats.sort_values(by=["Product", "Education_Type", "Metric Tested"])
print(Back.YELLOW +f"\nImpact on Contribution Margin (CM) of {change_percent}% improvement in metrics: {list(metrics_to_test.keys())}"+Style.RESET_ALL)
print(modified_product_stats)


##########################  Display Zero filtered  results
modified_product_stats = modified_product_stats[modified_product_stats.CM_Change > 0].sort_values(by="CM_Change", ascending=False)
print(Back.YELLOW +f"\nImpact on Contribution Margin (CM) of {change_percent}% improvement in metrics: {list(metrics_to_test.keys())}"+Style.RESET_ALL)
print("(Zero values filtered, sorted by improvement percentage)")
print(modified_product_stats)


Impact on Contribution Margin (CM) of 10% improvement in metrics: ['UA', 'LTC', 'AOV', 'C1', 'CLTC']
             Product Education_Type Metric Tested           CM  CM_Change
1     Data Analytics        Morning           AOV    24,538.10      10.67
1     Data Analytics        Morning            C1    22,307.36       0.61
1     Data Analytics        Morning          CLTC    22,171.43       0.00
1     Data Analytics        Morning           LTC    22,320.96       0.67
1     Data Analytics        Morning            UA    22,171.43       0.00
2  Digital Marketing        Evening           AOV   354,406.28      10.05
2  Digital Marketing        Evening            C1   322,187.53       0.04
2  Digital Marketing        Evening          CLTC   322,051.60       0.00
2  Digital Marketing        Evening           LTC   322,201.12       0.05
2  Digital Marketing        Evening            UA   322,051.60       0.00
3  Digital Marketing        Morning           AOV 2,561,241.59      10.01
3  Digital Marketing        Morning            C1 2,328,401.44       0.01
3  Digital Marketing        Morning          CLTC 2,328,265.51       0.00
3  Digital Marketing        Morning           LTC 2,328,415.04       0.01
3  Digital Marketing        Morning            UA 2,328,265.51       0.00
6       UX/UI Design        Evening           AOV   143,065.71      10.12
6       UX/UI Design        Evening            C1   130,059.74       0.10
6       UX/UI Design        Evening          CLTC   129,923.81       0.00
6       UX/UI Design        Evening           LTC   130,073.33       0.12
6       UX/UI Design        Evening            UA   129,923.81       0.00
7       UX/UI Design        Morning           AOV 1,078,742.57      10.02
7       UX/UI Design        Morning            C1   980,675.06       0.01
7       UX/UI Design        Morning          CLTC   980,539.13       0.00
7       UX/UI Design        Morning           LTC   980,688.66       0.02
7       UX/UI Design        Morning            UA   980,539.13       0.00
9      Web Developer        Morning           AOV   543,888.06      10.03
9      Web Developer        Morning            C1   494,443.69       0.03
9      Web Developer        Morning          CLTC   494,307.76       0.00
9      Web Developer        Morning           LTC   494,457.28       0.03
9      Web Developer        Morning            UA   494,307.76       0.00

Impact on Contribution Margin (CM) of 10% improvement in metrics: ['UA', 'LTC', 'AOV', 'C1', 'CLTC']
(Zero values filtered, sorted by improvement percentage)
             Product Education_Type Metric Tested           CM  CM_Change
1     Data Analytics        Morning           AOV    24,538.10      10.67
6       UX/UI Design        Evening           AOV   143,065.71      10.12
2  Digital Marketing        Evening           AOV   354,406.28      10.05
9      Web Developer        Morning           AOV   543,888.06      10.03
7       UX/UI Design        Morning           AOV 1,078,742.57      10.02
3  Digital Marketing        Morning           AOV 2,561,241.59      10.01
1     Data Analytics        Morning           LTC    22,320.96       0.67
1     Data Analytics        Morning            C1    22,307.36       0.61
6       UX/UI Design        Evening           LTC   130,073.33       0.12
6       UX/UI Design        Evening            C1   130,059.74       0.10
2  Digital Marketing        Evening           LTC   322,201.12       0.05
2  Digital Marketing        Evening            C1   322,187.53       0.04
9      Web Developer        Morning           LTC   494,457.28       0.03
9      Web Developer        Morning            C1   494,443.69       0.03
7       UX/UI Design        Morning           LTC   980,688.66       0.02
7       UX/UI Design        Morning            C1   980,675.06       0.01
3  Digital Marketing        Morning           LTC 2,328,415.04       0.01
3  Digital Marketing        Morning            C1 2,328,401.44       0.01

Image("Metrics_Tree.png")

Image("HADI-циклы.png")

##### Calculating confidence intervals and sample size for A|B testing first hypothesis
print(Back.YELLOW+"Calculation for the first hypothesis:"+Style.RESET_ALL)
print(Fore.BLUE+"Offering additional products when purchasing a course (advanced study materials, books,") 
print("certificates for online trainings on an advanced data analysis course, etc.)") 
print("will increase the Average Order Value by 10%. Confidence interval 5%: 1301.66 ± 32.54€" +Style.RESET_ALL)

p = 0.0002
aov = 1183.33
xi = 0.02
print(f"Base conversion rate p={p*100:.2f}%")
print(f"Minimum detectable effect xi={xi*100:.2f}%")
print(f"Base Average Order Value AOV={aov:.2f}%")

n = (16*p*(1-p))/xi**2
print(f"Required sample size for A|B testing: {int(n)} participants")
print(f"Total number of participants required for the experiment: {int(n)*2}")
aov_new = aov + aov*0.1
print(f"New AOV with confidence interval,€: {aov_new:.2f} ± {aov_new*0.025:.2f}€\n")

##### Calculating confidence intervals and sample size for A|B testing second hypothesis
print(Back.YELLOW+"Calculation for the second hypothesis:"+Style.RESET_ALL)
print(Fore.MAGENTA+'Introducing a referral program with the model "5% off 1 month of training for a referred client"') 
print('will increase conversion by 10%. Confidence interval 5%: 2.12 ± 0.05%'+Style.RESET_ALL)

p = 0.0192
xi = 0.02
print(f"Base conversion rate p={p*100:.2f}%")
print(f"Minimum detectable effect xi={xi*100:.2f}%")

n = (16*p*(1-p))/xi**2
print(f"Required sample size for A|B testing: {int(n)} participants")
print(f"Total number of participants required for the experiment: {int(n)*2} participants")
p_new = p*100 + p*100*0.1
print(f"New Conversion Rate with confidence interval,%: {p_new:.2f} ± {p_new*0.025:.2f}%")

Calculation for the first hypothesis:
Offering additional products when purchasing a course (advanced study materials, books,
certificates for online trainings on an advanced data analysis course, etc.)
will increase the Average Order Value by 10%. Confidence interval 5%: 1301.66 ± 32.54€
Base conversion rate p=0.02%
Minimum detectable effect xi=2.00%
Base Average Order Value AOV=1183.33%
Required sample size for A|B testing: 7 participants
Total number of participants required for the experiment: 14
New AOV with confidence interval,€: 1301.66 ± 32.54€

Calculation for the second hypothesis:
Introducing a referral program with the model "5% off 1 month of training for a referred client"
will increase conversion by 10%. Confidence interval 5%: 2.12 ± 0.05%
Base conversion rate p=1.92%
Minimum detectable effect xi=2.00%
Required sample size for A|B testing: 753 participants
Total number of participants required for the experiment: 1506 participants
New Conversion Rate with confidence interval,%: 2.11 ± 0.05%

import plotly.graph_objects as go

################ Calculation
deals["Deal_Created_Date_2W"] = deals["Created_Time"].dt.to_period("2W")
deals["Deal_Created_Date_M"] = deals["Created_Time"].dt.to_period("M")

deals_trend_2W = deals.groupby("Deal_Created_Date_2W")["Id"].count().reset_index()
deals_trend_2W.rename(columns={"Id": "Deals_Created"}, inplace=True)

deals_trend_M = deals.groupby("Deal_Created_Date_M")["Id"].count().reset_index()
deals_trend_M.rename(columns={"Id": "Deals_Created"}, inplace=True)

deals_trend_2W["Deal_Created_Date"] = deals_trend_2W["Deal_Created_Date_2W"].dt.to_timestamp()
deals_trend_M["Deal_Created_Date"] = deals_trend_M["Deal_Created_Date_M"].dt.to_timestamp()

deals_trend_2W = deals_trend_2W.query("Deal_Created_Date > '2022-12-31'")
deals_trend_M = deals_trend_M.query("Deal_Created_Date > '2022-12-31'")

print(Fore.BLUE+f"Average Deals number per two weeks period: {deals_trend_2W["Deals_Created"].mean():.0f}"+Style.RESET_ALL)
print(Fore.MAGENTA+f"Average Deals number per month period:: {deals_trend_M["Deals_Created"].mean():.0f}"+Style.RESET_ALL)

################ VISUALISATION
fig = go.Figure()

fig.add_trace(
    go.Scatter(
        x=deals_trend_2W["Deal_Created_Date"],
        y=deals_trend_2W["Deals_Created"],
        mode="lines+markers+text",
        text=deals_trend_2W["Deals_Created"],
        textposition="top center",
        textfont=dict(size=8, color="black"),
        name="Deals Created (2 Weeks)",
        line=dict(color="blue", width=2),
        marker=dict(size=6, color="blue"),
        visible=True
    )
)


fig.add_trace(
    go.Scatter(
        x=deals_trend_M["Deal_Created_Date"],
        y=deals_trend_M["Deals_Created"],
        mode="lines+markers+text",
        text=deals_trend_M["Deals_Created"],
        textposition="top center",
        textfont=dict(size=8, color="black"),
        name="Deals Created (Monthly)",
        line=dict(color="blue", width=2, dash="dash"),
        marker=dict(size=6, color="blue"),
        visible=False
    )
)

# drop-down switch
fig.update_layout(
    updatemenus=[
        dict(
            buttons=[
                dict(label="2 Weeks", method="update", args=[{"visible": [True, False]}]),
                dict(label="Monthly", method="update", args=[{"visible": [False, True]}]),
            ],
            direction="down",
            showactive=True,
            x=0.2, y=1.1
        )
    ],
    title="Deals Trends (Selectable Periods)",
    xaxis_title="Period",
    yaxis_title="Deals Number",
    template="plotly_white",
    hovermode="x unified",
    legend=dict(x=0.9, y=1.18, title="Metrics")
)

fig.show()

Average Deals number per two weeks period: 415
Average Deals number per month period:: 1799

import scipy.stats as stats
"""
Hypothesis №1
Offering additional products when purchasing a course (advanced study materials, books,
certificates for online trainings on an advanced data analysis course, etc.)
will increase the Average Order Value by 10%. Confidence interval 5%: 1.301,66 ± 32.54€

Null Hypothesis №1
Offers of additional products when purchasing a course do not affect the Average Order Value.
"""
# Initial data
AOV_base = 1183.33  # Average Order Value without additional products
AOV_test = AOV_base * 1.10  # Average Order Value with a 10% increase

std_base = 29.58  # Standard deviation of AOV in the control group
std_test = 32.54  # Standard deviation of AOV in the test group

n_A = 7  # Sample size of group A (control)
n_B = 7  # Sample size of group B (test)

# Generate samples
np.random.seed(42)
sample_A = np.random.normal(AOV_base, std_base, n_A)
sample_B = np.random.normal(AOV_test, std_test, n_B)

# Perform Student's t-test
t_statistic, p_value = stats.ttest_ind(sample_A, sample_B, equal_var=False)

# Output results
print(f"T-statistic: {t_statistic:.4f}")
print(f"P-value: {p_value:.4f}")

# Interpret results
alpha = 0.05  # Significance level (5%)
if p_value < alpha:
    print(Fore.GREEN +"Hypothesis NOT rejected: AOV increase by 10% is statistically significant."+Style.RESET_ALL)
else:
    print(Fore.RED +"Hypothesis rejected: Difference in AOV is not statistically significant."+Style.RESET_ALL)

T-statistic: -6.7227
P-value: 0.0000
Hypothesis NOT rejected: AOV increase by 10% is statistically significant.

import scipy.stats as stats

"""
Hypothesis №2
Introducing a referral program with the model "5% off 1 month of training for a referred client"
will increase conversion rate by 10%. Confidence interval 5%: 2.12 ± 0.05%

Null Hypothesis №2
The introduction of a referral program with the model "5% for 1 month of training 
for an invited client" does not affect conversion rate
"""

# Initial data
C1_base = 1.93   # текущая конверсия
C1_test = C1_base * 1.10  # Average Order Value with a 10% increase

std_base = 0.048  # Standard deviation of AOV in the control group
std_test = 0.051  # Standard deviation of AOV in the test group

n_A = 753  # Sample size of group A (control)
n_B = 753  # Sample size of group B (test)

# Generate samples
np.random.seed(42)
sample_A = np.random.normal(C1_base, std_base, n_A)
sample_B = np.random.normal(C1_test, std_test, n_B)

# Perform Student's t-test
t_statistic, p_value = stats.ttest_ind(sample_A, sample_B, equal_var=False)

# Output results
print(f"T-statistic: {t_statistic:.4f}")
print(f"P-value: {p_value:.4f}")

# Interpret results
alpha = 0.05  # Significance level (5%)
if p_value < alpha:
    print(Fore.GREEN +"Hypothesis NOT rejected: C1 increase by 10% is statistically significant."+Style.RESET_ALL)
else:
    print(Fore.RED +"Hypothesis rejected: Difference in C1 is not statistically significant."+Style.RESET_ALL)

T-statistic: -79.4407
P-value: 0.0000
Hypothesis NOT rejected: C1 increase by 10% is statistically significant.

	Product	B	T	AOV	Revenue	UA	AC	LTC	C1	CLTC	APC	CLTV	LTV	CM
0	Data Analytics	3	20.00	1,183.33	23,200.00	18548	149,523.45	8.06	0.02	498.41	6.67	7,888.89	127.60	22,171.43
1	Digital Marketing	471	2,877.00	939.66	2,463,200.91	18548	149,523.45	8.06	2.54	3.17	6.11	5,739.70	14,575.16	2,701,905.24
2	UX/UI Design	228	1,170.00	944.86	1,032,559.55	18548	149,523.45	8.06	1.23	6.56	5.13	4,848.65	5,960.16	1,103,996.13
3	Web Developer	137	505.00	981.79	436,696.67	18548	149,523.45	8.06	0.74	10.91	3.69	3,619.00	2,673.08	494,307.76
4	TOTAL:	839.00	4,572.00	865.19	3,955,657.13	18,548.00	149,523.45	8.06	0.05	178.22	5.45	4,714.73	213.27	3,806,133.68

	Product	Education_Type	B	T	AOV	Revenue	UA	AC	LTC	C1	CLTC	APC	CLTV	LTV	CM
0	Data Analytics	Morning	3	20.00	1,183.33	23,200.00	18548	149,523.45	8.06	0.02	498.41	6.67	7,888.89	127.60	22,171.43
1	Digital Marketing	Evening	113	764.00	423.49	288,181.82	18548	149,523.45	8.06	0.61	13.23	6.76	2,863.25	1,744.38	322,051.60
2	Digital Marketing	Morning	358	2,113.00	1,102.58	2,175,019.09	18548	149,523.45	8.06	1.93	4.18	5.90	6,507.71	12,560.71	2,328,265.51
3	UX/UI Design	Evening	57	284.00	462.74	116,798.63	18548	149,523.45	8.06	0.31	26.23	4.98	2,305.60	708.53	129,923.81
4	UX/UI Design	Morning	171	886.00	1,108.39	915,760.92	18548	149,523.45	8.06	0.92	8.74	5.18	5,742.89	5,294.56	980,539.13
5	Web Developer	Morning	137	505.00	981.79	436,696.67	18548	149,523.45	8.06	0.74	10.91	3.69	3,619.00	2,673.08	494,307.76
6	TOTAL:		839.00	4,572.00	865.19	3,955,657.13	18,548.00	149,523.45	8.06	0.05	178.22	5.45	4,714.73	213.27	3,806,133.68

8. Задание по продуктовой аналитике¶

8.1. Расчет юнит-экономики по продуктам¶

Определение количества лидов(UA), маркетингового бюджета (AC) и стоимости привлечения лида (LTC)¶

Рассчет метрик юнит-экономики про продуктам (предлагаемый курс обучения)¶

Рассчет метрик юнит-экономики про продуктам (предлагаемый курс обучения) и типам обучения (утренний курс/вечерний курс)¶

Визуализируем маржинальную прибыль по продуктам¶

Анализ юнит-экономики образовательных продуктов¶

8.2. Определение точек роста бизнеса¶

8.3. Дерево метрик для бизнеса¶

8.4. Формулирование гипотез для метрик продукта¶

8.5. Описание метода проверки гипотез с формулированием условий проверки¶

HADI-циклы для проверки гипотез¶

Расчет доверительных интервалов и размера выборки для проведения А|B тестирования¶

Формулирование исходные данных для экспериментов¶

Проверим срок проведения тестирования¶

Моделирование проведения A|B теста¶

Гипотеза №1 - проверка по t-критерию Стьюдента¶

Гипотеза №2 - проверка по t-критерию Стьюдента¶